#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
@Author:    Colin
@Github:    https://github.com/opscolin
@Gitee:     https://gitee.com/colin5063
@DateTime:  2022/9
@File:      config.py
@Software:  PyCharm
"""

import yaml
import pymysql
from config import RDS_USER, RDS_PASSWORD


def get_config(filename):
    with open(filename, "r") as ymlfile:
        cfg = yaml.safe_load(ymlfile)
    return cfg


def get_instance_id(instance_name):
    cfg = get_config('instance.yml')
    instance = cfg[instance_name]
    return instance['id']


def get_mysql_conn(instance_name):
    cfg = get_config('instance.yml')
    instance = cfg[instance_name]
    host = instance['host']
    conn = pymysql.connect(host=host, user=RDS_USER, password=RDS_PASSWORD, port=3306)
    return conn


def execute_sql(instance_name, sql):
    try:
        conn = get_mysql_conn(instance_name)
        cursor = conn.cursor()
        cursor.execute(sql)
        return cursor.fetchall()
    except Exception as e:
        print(f"Execute sql error: {e}")


def get_mysql_dbs(instance_name):
    sql = """
        select 
            distinct TABLE_SCHEMA 
        from 
            information_schema.tables  
        where 
            table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema');
    """
    res = execute_sql(instance_name, sql)
    return res


def get_mysql_dbs_tables(instance_name):
    sql = """
        select 
            TABLE_SCHEMA, table_name 
        from 
            information_schema.tables  
        where 
            table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema');
    """
    res = execute_sql(instance_name, sql)
    return res


def get_mysql_db_tables(instance_name, dbname):
    sql = f"""
        select 
            table_name 
        from 
            information_schema.tables  
        where 
            table_schema = '{dbname}';
    """
    res = execute_sql(instance_name, sql)
    return res


def get_mysql_dbsize(instance_name, dbname):
    sql = f"""
        select 
            sum(truncate(data_length + index_length, 2)) as size
        from 
            information_schema.tables
        where table_schema = '{dbname}';
    """

    res = execute_sql(instance_name, sql)
    return res[0][0]


def get_mysql_tablesize(instance_name, dbname, tablename):
    sql = f"""
        select 
            sum(truncate(data_length + index_length, 2)) as size
        from 
            information_schema.tables
        where 
            table_schema = '{dbname}' and table_name = '{tablename}'
    """
    res = execute_sql(instance_name, sql)
    return res[0][0]


def get_mysql_variable_values(instance_name, var_type='variables', variable_name='None'):
    """ 获取指定变量 show global variables 或者 show global status的值"""
    conn = get_mysql_conn(instance_name)
    cursor = conn.cursor()
    if variable_name:
        sql = f"show global {var_type} where variable_name='{variable_name}';"
    else:
        sql = f"show global {var_type} ;"

    cursor.execute(sql)
    return cursor.fetchall()
